<?php
/* -----------------------------------------------------------------
 *                        Aveghe My Blog
 * -----------------------------------------------------------------
 * Aveghe My Blog: made your blog easy!
 * Copyright (C) 2008 Elia Contini
 * 
 * Aveghe My Blog is free software; you can redistribute it and/or
 * modify it under the terms of the GNU General Public License
 * as published by the Free Software Foundation; either version 2
 * of the License, or (at your option) any later version.
 * 
 * Aveghe My Blog is distributed in the hope that it will be useful,
 * but WITHOUT ANY WARRANTY; without even the implied warranty of
 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
 * GNU General Public License for more details.
 * 
 * You should have received a copy of the GNU General Public License
 * along with this program; if not, write to the Free Software
 * Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston,
 * MA 02110-1301, USA.
 */

require_once('Aveghe/Exception.php');

class Doc
	{
		private $db;
		private $tablePrefix;
		
		public function __construct()
			{
				$this->db = Zend_Registry::get('db');
				$this->tablePrefix =  Zend_Registry::get('tablePrefix');
			}
			
		public function countFiles()
			{
				$sql = 'SELECT COUNT(*) FROM '.$this->tablePrefix.'documents WHERE document_type = "-";';
				$row = $this->db->fetchRow($sql);

				/*while(list($key, $value) = each($row))
							echo "$key : $value <br />"; die;*/
				return $row['COUNT(*)'];
			}
			
		public function getCommentsCounter($fileID)
			{
				$sql = 'SELECT document_comments_counter FROM '.$this->tablePrefix.'documents WHERE document_id = "'.(int) $fileID.'";';
				$row = $this->db->fetchRow($sql);
				
				return (int) $row['document_comments_counter'];
			}
			
		public function getFilesWithLimit($offset, $numRecord)
			{
				$sql  = 'SELECT document_id, document_name, document_description, document_content, document_creation_date, document_keywords, document_comments_enable, document_comments_counter, user_id, user_name, user_surname ';
				$sql .= 'FROM '.$this->tablePrefix.'documents d, '.$this->tablePrefix.'users u ';
				$sql .= 'WHERE d.document_type="-" AND d.document_owner = u.user_id ';
				$sql .= 'ORDER BY d.document_creation_date DESC LIMIT '.(int) $offset.','.(int) $numRecord.';';

				//echo $sql; die; 
				$rows = $this->db->fetchAll($sql);
					
				return $rows;
			}
		
		public function getDirectoryFilesWithLimit($directoryID, $offset, $numRecord)
			{
				// files in current category
				$sql = 'SELECT document_id FROM '.$this->tablePrefix.'documents WHERE document_parent_directory="'.(int) $directoryID.'" AND document_type="-";';
				$files = $this->db->fetchAll($sql);
				
				// subCategories
				$sql = 'SELECT document_id FROM '.$this->tablePrefix.'documents WHERE document_parent_directory="'.(int) $directoryID.'" AND document_type="d";';
				$subCategories = $this->db->fetchAll($sql);

				$rows = NULL;
				if(count($files) > 0)
					{
						$sql  = 'SELECT document_id, document_name, document_description, document_content, document_modification_date, document_keywords, document_comments_enable, document_comments_counter, user_id, user_name, user_surname ';
						$sql .= 'FROM '.$this->tablePrefix.'documents d, '.$this->tablePrefix.'users u ';
						$sql .= 'WHERE d.document_type="-" AND d.document_owner = u.user_id AND d.document_parent_directory="'.(int) $directoryID.'" ';
						$sql .= 'ORDER BY d.document_modification_date DESC LIMIT '.(int) $offset.','.(int) $numRecord.';';

						$rows = $this->db->fetchAll($sql);
					}
				else
					{
						$sql  = 'SELECT document_id, document_name, document_description, document_content, document_modification_date, document_keywords, document_comments_enable, document_comments_counter, user_id, user_name, user_surname ';
						$sql .= 'FROM '.$this->tablePrefix.'documents d, '.$this->tablePrefix.'users u ';
						$sql .= 'WHERE d.document_type="-" AND d.document_owner = u.user_id AND (d.document_parent_directory="'.(int) $directoryID.'" ';
						
						foreach($subCategories as $subCategory)
							{
								$sql .= 'OR d.document_parent_directory="'.(int) $subCategory['document_id'].'" ';
							}
						
						$sql .= ') ORDER BY d.document_modification_date DESC LIMIT '.(int) $offset.','.(int) $numRecord.';';

						$rows = $this->db->fetchAll($sql);
					}
					
				return $rows;
			}
			
		public function getFolder($folderID)
			{
				$sql = 'SELECT * FROM '.$this->tablePrefix.'documents WHERE document_parent_directory="'.(int) $folderID.'" ORDER BY document_name;';
				$rows = $this->db->fetchAll($sql);
	
				return $rows;
			}
		
		public function getDirectories()
			{
				$table = ($this->tablePrefix).'documents';
				$sql = "
					SELECT document_id, document_parent_directory, document_name 
					FROM $table
					WHERE document_type = 'd'
					ORDER BY document_parent_directory ASC, document_name;
				";
				$rows = $this->db->fetchAll($sql);
	
				return $rows;
			}
			
		public function getParentFolder($folderID)
			{
				$sql = 'SELECT document_parent_directory FROM '.$this->tablePrefix.'documents WHERE document_id ="'.(int) $folderID.'";';
				$row = $this->db->fetchRow($sql);
	
				return $row;
			}
			
		public function getFile($fileID)
			{
				$sql = 'SELECT * FROM '.$this->tablePrefix.'documents WHERE document_id="'.(int) $fileID.'";';
				$rows = $this->db->fetchRow($sql);
	
				return $rows;
			}
		
		public function getFileWithAuthorInfo($fileID)
			{
				$sql  = 'SELECT document_id, document_name, document_parent_directory, document_content, document_creation_date, document_keywords, document_comments_enable, user_id, user_name, user_surname ';
				$sql .= 'FROM '.$this->tablePrefix.'documents d, '.$this->tablePrefix.'users u ';
				$sql .= 'WHERE d.document_id = '.$fileID.' AND d.document_owner = u.user_id;';
				$row = $this->db->fetchRow($sql);
	
				return $row;
			}
			
		public function incrementFileCommentsCounter($fileID)
			{
				// TODO - (Aveghe) start transaction
				$sql = 'SELECT document_comments_counter FROM '.$this->tablePrefix.'documents WHERE document_id = "'.(int) $fileID.'";';
				$row = $this->db->fetchRow($sql);
					
				$counter = (int) $row['document_comments_counter'] + 1;
				
				$document = array('document_comments_counter' => $counter);
				$where = 'document_id = '.(int) $fileID;
						
				$this->db->update($this->tablePrefix.'documents', $document, $where);
				// end trasaction
			}
		
		public function insertUpdateDocument($documentID, $document)
			{
				$sql = 'SELECT document_id FROM '.$this->tablePrefix.'documents WHERE document_parent_directory = "'.(int) $document['document_parent_directory'].'" AND document_name = "'.$document['document_name'].'";';
				$rows = $this->db->fetchAll($sql);
					
				if(!isset($documentID) || $documentID == '')
					{
						if(count($rows) != 0)
							throw new Aveghe_File_Already_Exists_Exception($document['document_name'].' already exists!');
							
						// new document (insert)
						$document['document_creation_date'] = new Zend_Db_Expr('NOW()');
						$document['document_modification_date'] = new Zend_Db_Expr('NOW()');
						$this->db->insert($this->tablePrefix.'documents', $document);
					}
				else
					{
						if(count($rows) == 1 && $rows[0]['document_id'] != $documentID)
							throw new Aveghe_File_Already_Exists_Exception($document['document_name'].' already exists!');
						
						// document alredy exist (update)
						$document['document_modification_date'] = new Zend_Db_Expr('NOW()');
						$where = 'document_id = '.(int) $documentID;
						
						/*while(list($key, $value) = each($document))
							echo "$key : $value <br />"; die;*/
						
						$this->db->update($this->tablePrefix.'documents', $document, $where);
					}
			}
		
		public function countSearchResult($searchKeys)
			{
				$sql  = 'SELECT COUNT(*) ';
				$sql .= 'FROM '.$this->tablePrefix.'documents ';
				$sql .= 'WHERE document_type="-" AND (';
				
				// search in file name, content and keywords
				for($i = 0; $i < count($searchKeys); $i++)
					{
						$sql .= 'document_name LIKE "%'.$searchKeys[$i].'%" OR ';
						$sql .= 'document_content LIKE "%'.$searchKeys[$i].'%" OR ';
						$sql .= 'document_keywords LIKE "%'.$searchKeys[$i].'%"';
						if(($i + 1) < count($searchKeys))
							$sql .= ' OR ';
					}
					
				$sql .= ');';

				$row = $this->db->fetchRow($sql);
				
				return $row['COUNT(*)'];
			}
		
		public function search($searchKeys, $offset, $recordNum)
			{
				$sql  = 'SELECT document_id, document_name ';
				$sql .= 'FROM '.$this->tablePrefix.'documents ';
				$sql .= 'WHERE document_type="-" AND (';
				
				// search in file name, content and keywords
				for($i = 0; $i < count($searchKeys); $i++)
					{
						$sql .= 'document_name LIKE "%'.$searchKeys[$i].'%" OR ';
						$sql .= 'document_content LIKE "%'.$searchKeys[$i].'%" OR ';
						$sql .= 'document_keywords LIKE "%'.$searchKeys[$i].'%"';
						if(($i + 1) < count($searchKeys))
							$sql .= ' OR ';
					}
				$sql .= ') ORDER BY document_modification_date DESC LIMIT '.(int) $offset.','.(int) $recordNum.';';
				
				$rows = $this->db->fetchAll($sql);
					
				return $rows;
			}
	}
?>